﻿using Dapper.Library;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Expressions;

namespace Dapper.MsSql.Library
{
    public class MsSqlProvider : SqlProvider
    {
        /// <summary>
        /// 参数信息
        /// </summary>
        private DynamicParameters parameters = null;

        #region SQL拼装信息
        //执行语句
        string executeSql = string.Empty;
        //topN
        int? topNum;
        //表名称
        string fromTableSql = string.Empty;
        //无锁SQL
        string nolockSql = string.Empty;
        //参数
        WhereExpression whereParams = null;
        //条件语句
        string whereSql = string.Empty;
        //排序语句
        string orderbySql = string.Empty;

        #endregion

        /// <summary>
        /// 分页用到的sql语句
        /// </summary>
        private const string PAGINATION_SQL = @"SELECT	COUNT (1) FROM ({0}) ForMaxCount;--最大条数
                                                SELECT	* FROM(
				                                            SELECT
					                                            ROW_NUMBER () OVER ({1}) AS ROWNUM ,*
				                                            FROM
					                                            ({0}) AS ret
			                                            ) AS query
		                                            WHERE
			                                            query.ROWNUM >  {2}
		                                            AND query.ROWNUM <= {3};";

        /// <summary>
        /// 分页用到的sql语句
        /// </summary>
        private const string PAGINATION_SQL1 = @"   DECLARE	@MaxCount INT 
                                                    SELECT	@MaxCount = COUNT (1) FROM ({0}) AS ForMaxCount--最大条数
                                                    SELECT	*,@MaxCount MaxCount
		                                                    FROM(
				                                                    SELECT
					                                                    ROW_NUMBER () OVER ({1}) AS ROWNUM ,*
				                                                    FROM
					                                                    ({0}) AS ret
			                                                    ) AS query
		                                                    WHERE
			                                                    query.ROWNUM >  {2}
		                                                    AND query.ROWNUM <= {3};";

        /// <summary>
        /// 开始符号
        /// </summary>
        private const char OpenQuote = '[';

        /// <summary>
        /// 结束符号
        /// </summary>
        private const char CloseQuote = ']';

        /// <summary>
        /// 匹配符前缀
        /// </summary>
        private const char ParameterPrefix = '@';

        public MsSqlProvider()
        {
            SymbolInfo = new SymbolInfo(OpenQuote, CloseQuote, ParameterPrefix);
            ResolveExpression.InitOption(SymbolInfo);
        }

        protected sealed override SymbolInfo SymbolInfo { get; set; }


        /// <summary>
        /// 拼装单条查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override SqlProvider FormatToSingle<T>()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, 1);
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql}";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = SetContext.SqlString;
                Params = parameters;

                SqlString = $"{executeSql}";
            }
            return this;
        }

        /// <summary>
        /// 拼装多条查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public override SqlProvider FormatToList<T>()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                topNum = SetContext.TopNum;
                executeSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, topNum);
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} {orderbySql}";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = SetContext.SqlString;
                Params = parameters;

                SqlString = $"{executeSql}";
            }
            return this;
        }

        /// <summary>
        /// 拼装分页查询SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public override SqlProvider FormatToPageList<T>(int pageIndex, int pageSize)
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);
                if (string.IsNullOrEmpty(orderbySql))
                {
                    throw new Exception("排序方式优先于页列表");
                }
                executeSql = ResolveExpression.ResolveSelect(typeof(T).GetProperties(), SetContext.SelectExpression, pageSize);
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;

                SqlString = $"SELECT COUNT(1) {fromTableSql} {nolockSql} {whereSql};";
                SqlString += $@"{executeSql}
                        FROM    ( SELECT *
                                  ,ROW_NUMBER() OVER ( {orderbySql} ) AS ROWNUMBER
                                  {fromTableSql} {nolockSql}
                                  {whereSql}
                                ) T
                        WHERE   ROWNUMBER > {(pageIndex - 1) * pageSize}
                                AND ROWNUMBER <= {pageIndex * pageSize} {orderbySql};";

            }
            else
            {

                orderbySql = ResolveExpression.ResolveOrderBy(SetContext.OrderbyList);
                if (string.IsNullOrEmpty(orderbySql))
                {
                    throw new Exception("排序方式优先于页列表");
                }
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = SetContext.SqlString;
                Params = parameters;

                var interfaceType = typeof(T).GetInterface("IDictionary");
                if (typeof(T).Name.IndexOf("IDictionary") >= 0 || interfaceType != null)
                {
                    //分页查询语句
                    SqlString = string.Format(PAGINATION_SQL1,
                                                executeSql,
                                                orderbySql,
                                               (pageIndex - 1) * pageSize,
                                                pageIndex * pageSize);
                }
                else
                {
                    //分页查询语句
                    SqlString = string.Format(PAGINATION_SQL,
                                                executeSql,
                                                orderbySql,
                                                (pageIndex - 1) * pageSize,
                                                pageIndex * pageSize);
                }
            }
            return this;
        }


        /// <summary>
        /// 获取COUNT()函数SQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatCount()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = "SELECT COUNT(1)";
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} ";
            }
            else
            {

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = $"SELECT COUNT(1) FROM ({SetContext.SqlString}) ForCount";
                Params = parameters;

                SqlString = $"{executeSql}";
            }
            return this;
        }

        /// <summary>
        /// 获取Exists()函数SQL
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatExists()
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = "SELECT COUNT(1) ";
                fromTableSql = FormatTableName();
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;

                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql}";
            }
            else
            {
                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);
                executeSql = $"SELECT COUNT(1) FROM ({SetContext.SqlString}) FormatExists";
                Params = parameters;

                SqlString = $"{executeSql}";
            }

            return this;
        }

        /// <summary>
        ///  获取DeleteSQL 0nosql 1sql
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatDelete()
        {
            fromTableSql = FormatTableName();
            whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            whereSql = whereParams.SqlCmd;
            Params = whereParams.Param;
            SqlString = $"DELETE {fromTableSql} {whereSql}";

            return this;
        }

        /// <summary>
        /// 获取ExecuteNoQuerySQ
        /// </summary>
        /// <returns></returns>
        public override SqlProvider FormatExecuteNoQuery()
        {
            parameters = new DynamicParameters();
            parameters.AddDynamicParams(SetContext.Params);
            executeSql = SetContext.SqlString;
            Params = parameters;

            SqlString = $"{executeSql}";
            return this;
        }

        /// <summary>
        /// 获取Insert-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override SqlProvider FormatInsert<T>(T entity)
        {
            var paramsAndValuesSql = FormatInsertParamsAndValues(entity);

            if (SetContext.IfNotExistsExpression == null)
                SqlString = $"INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) VALUES({paramsAndValuesSql[1]})";
            else
            {
                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "INT_");

                SqlString = $"IF NOT EXISTS (SELECT 1 FROM { FormatTableName(false)} {ifnotexistsWhere.SqlCmd}) " +
                    $" INSERT INTO {FormatTableName(false)} ({paramsAndValuesSql[0]}) VALUES ({paramsAndValuesSql[1]})";

                Params.AddDynamicParams(ifnotexistsWhere.Param);
            }
            return this;
        }

        /// <summary>
        /// 获取Update-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateExpression"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdate<T>(Expression<Func<T, T>> updateExpression)
        {
            var update = ResolveExpression.ResolveUpdate(updateExpression);
            var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            if (SetContext.IfNotExistsExpression == null)
                SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
            else
            {
                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "UPDATE_");
                string sqlCmd = ifnotexistsWhere.SqlCmd.Replace("UPDATE_gCode", "gCode");

                SqlString = $"IF NOT EXISTS (SELECT 1 FROM { FormatTableName(false)} {sqlCmd}) " +
                    $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
            }

            return this;

        }

        /// <summary>
        /// 获取Update-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdate<T>(T entity)
        {
            var update = ResolveExpression.ResolveUpdate<T>(a => entity);
            var where = ResolveExpression.ResolveWhere(entity);
            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            if (SetContext.IfNotExistsExpression == null) {
                SqlString = $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
            }
            else
            {
                var ifnotexistsWhere = ResolveExpression.ResolveWhere(SetContext.IfNotExistsExpression, "UPDATE_");
                string sqlCmd = ifnotexistsWhere.SqlCmd.Replace("UPDATE_gCode", "gCode");

                SqlString = $"IF NOT EXISTS (SELECT 1 FROM { FormatTableName(false)} {sqlCmd}) " +
                    $"UPDATE {FormatTableName(false)} {update.SqlCmd} {whereSql}";
            }
            return this;
        }

        /// <summary>
        /// 获取Sum-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="lambdaExpression"></param>
        /// <returns></returns>
        public override SqlProvider FormatSum<T>(LambdaExpression lambdaExpression)
        {
            if (string.IsNullOrWhiteSpace(SetContext.SqlString))
            {
                executeSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
                fromTableSql = FormatTableName();
                whereParams = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
                nolockSql = ResolveExpression.ResolveWithNoLock(SetContext.NoLock);
                whereSql = whereParams.SqlCmd;
                Params = whereParams.Param;
                SqlString = $"{executeSql} {fromTableSql} {nolockSql} {whereSql} ";
            }
            else
            {

                parameters = new DynamicParameters();
                parameters.AddDynamicParams(SetContext.Params);

                Params = parameters;
                executeSql = ResolveExpression.ResolveSum(typeof(T).GetProperties(), lambdaExpression);
                SqlString = $"{executeSql} FROM ({SetContext.SqlString}) FormatSum";
            }

            return this;
        }

        /// <summary>
        /// 获取先Update-再Select-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updator"></param>
        /// <returns></returns>
        public override SqlProvider FormatUpdateSelect<T>(Expression<Func<T, T>> updator)
        {
            var update = ResolveExpression.ResolveUpdate(updator);
            var executeSql = ResolveExpression.ResolveSelectOfUpdate(typeof(T).GetProperties(), SetContext.SelectExpression);
            var where = ResolveExpression.ResolveWhere(SetContext.WhereExpression);
            var whereSql = where.SqlCmd;

            Params = where.Param;
            Params.AddDynamicParams(update.Param);

            var topNum = SetContext.TopNum;
            //仅可在运行于 READ COMMITTED 或 REPEATABLE READ 隔离级别的事务中指定 READPAST。
            //在从 SNAPSHOT 隔离级别操作的事务中指定时，READPAST 必须与需要锁的其他表提示（例如，UPDLOCK 和 HOLDLOCK）组合。
            var topSql = topNum.HasValue ? $" TOP ({topNum.Value})" : "";
            SqlString = $"UPDATE {topSql} {FormatTableName(false)} WITH ( UPDLOCK, READPAST ) {update.SqlCmd} {executeSql} {whereSql}";

            return this;
        }

        /// <summary>
        /// 获取批量插入-SQL
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conn"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public override SqlProvider ExcuteBulkCopy<T>(IDbConnection conn, IEnumerable<T> list)
        {
            var dt = list.ToDataTable();

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            using (var sqlbulkcopy = new SqlBulkCopy((SqlConnection)conn))
            {
                sqlbulkcopy.DestinationTableName = dt.TableName;
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                }
                sqlbulkcopy.WriteToServer(dt);
            }

            return this;
        }
    }
}
